package com.fly.estore.dao.Impl;

import com.fly.estore.dao.BaseDAO;
import com.fly.estore.dao.SaleDetailDAO;
import com.fly.estore.domain.ProductCatalog;
import com.fly.estore.domain.ProductInfo;
import com.fly.estore.domain.ProductSKU;
import com.fly.estore.domain.SaleDetail;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.CollectionUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Fly on 2016/4/28.
 */
@Repository("SaleDetailDAO")
public class SaleDetailDAOImpl extends BaseDAO<SaleDetail> implements SaleDetailDAO {

    public int addSaleDetail(final SaleDetail detail) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("insert into sale_detail(sale_detail_id, sale_id, amount, price, product_id, product_sku_id)")
                .append("values(?, ?, ?, ?, ?, ?)");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setString(i++, detail.getSaleDetailId());
                ps.setString(i++, detail.getSaleId());
                ps.setInt(i++, detail.getAmount());
                ps.setFloat(i++, detail.getPrice());
                ps.setString(i++, detail.getProduct().getProductId());
                ps.setString(i++, detail.getProductSKU().getProductSkuId());
            }
        });
        return update;
    }

    public int updateSaleDetail(final SaleDetail detail) {
        int update = 0;
        StringBuilder sql = new StringBuilder();
        sql.append("update sale_detail set ")
                .append(" amount = ?,")
                .append(" price = ?,")
                .append(" product_id = ?,")
                .append(" product_sku_id = ?")
                .append(" where sale_detail_id = ?");
        update = getJdbcTemplate().update(sql.toString(), new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                int i = 1;
                ps.setInt(i++, detail.getAmount());
                ps.setFloat(i++, detail.getPrice());
                ps.setString(i++, detail.getProduct().getProductId());
                ps.setString(i++, detail.getProductSKU().getProductSkuId());
                ps.setString(i++, detail.getSaleDetailId());
            }
        });
        return update;
    }

    public int deleteById(final String saleDetailId) {
        int update = 0;
        String sql = "delete from sale_detail where sale_detail_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, saleDetailId);
            }
        });
        return update;
    }

    public int deleteBySaleId(final String saleId) {
        int update = 0;
        String sql = "delete from sale_detail where sale_id = ?";
        update = getJdbcTemplate().update(sql, new PreparedStatementSetter() {
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setString(1, saleId);
            }
        });
        return update;
    }

    public SaleDetail getById(String saleDetailId) {
        SaleDetail saleDetail = new SaleDetail();
        StringBuilder sql = new StringBuilder();
        sql.append("select sd.sale_detail_id, sd.sale_id, sd.amount, sd.price,")
                .append(" p.product_id, p.product_name, p.product_code, p.product_catalog_id,")
                .append(" ps.product_sku_id, ps.sku_name, ps.sku_code")
                .append(" from sale_detail sd")
                .append(" left join product_info p on sd.product_id = p.product_id")
                .append(" left join product_sku ps on sd.product_sku_id = ps.product_sku_id")
                .append(" where sale_detail_id = ?");
        List<SaleDetail> resultList = getJdbcTemplate().query(sql.toString(), new SaleDetailRowMapper(), new Object[]{saleDetailId});
        if (!CollectionUtils.isEmpty(resultList)){
            saleDetail = resultList.get(0);
        }
        return saleDetail;
    }

    public List<SaleDetail> getBySaleId(String saleId) {
        List<SaleDetail> saleDetailList = new ArrayList<SaleDetail>();
        StringBuilder sql = new StringBuilder();
        sql.append("select sd.sale_detail_id, sd.sale_id, sd.amount, sd.price,")
                .append(" p.product_id, p.product_name, p.product_code,  p.product_catalog_id,")
                .append(" ps.product_sku_id, ps.sku_name, ps.sku_code")
                .append(" from sale_detail sd")
                .append(" left join product_info p on sd.product_id = p.product_id")
                .append(" left join product_sku ps on sd.product_sku_id = ps.product_sku_id")
                .append(" where sale_id = ?");
        saleDetailList = getJdbcTemplate().query(sql.toString(), new SaleDetailRowMapper(), new Object[] {saleId});
        return saleDetailList;
    }

    public class SaleDetailRowMapper implements RowMapper<SaleDetail> {
        public SaleDetail mapRow(ResultSet rs, int rowNum) throws SQLException {
            SaleDetail detail = new SaleDetail();
            detail.setSaleDetailId(rs.getString("sale_detail_id"));
            detail.setSaleId(rs.getString("sale_id"));
            detail.setAmount(rs.getInt("amount"));
            detail.setPrice(rs.getFloat("price"));
            ProductInfo product = new ProductInfo();
            product.setProductId(rs.getString("product_id"));
            product.setProductName(rs.getString("product_name"));
            product.setProductCode(rs.getString("product_code"));
            ProductCatalog catalog = new ProductCatalog();
            catalog.setProductCatalogId(rs.getString("product_catalog_id"));
            product.setCatalog(catalog);
            detail.setProduct(product);
            ProductSKU sku = new ProductSKU();
            sku.setProductSkuId(rs.getString("product_sku_id"));
            sku.setSkuName(rs.getString("sku_name"));
            sku.setSkuCode(rs.getString("sku_code"));
            detail.setProductSKU(sku);
            return detail;
        }
    }
}
